/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAL;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import xmlobject.DTO.Categories.Categories;
import xmlobject.DTO.Categories.Category;

/**
 *
 * @author minhsong
 */
public class CategoryDAL extends Dataconection {

    private Category ReadCategoryFromDS(ResultSet rs){
        Category result = new Category();
        if(rs!=null){
            try {
                result.setCatogeryID(rs.getLong("CatogeryID"));
                result.setCatogeryName(rs.getString("CatogeryName"));
            } catch (SQLException ex) {
                Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return result;
    }
    
    public Category getCategoryById(int id) {
        try {
            proc_stmt = DAOConnection.prepareCall("{ call spcatogeryGet(?) }");
             proc_stmt.setLong("CatogeryID", id);
            rs = proc_stmt.executeQuery();
            if(rs.next())
            {
                return  ReadCategoryFromDS(rs);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public Categories getAllCategory() {
        Categories result = new Categories();
        try {
            proc_stmt = DAOConnection.prepareCall("{ call spcatogeryGetList() }");
            rs = proc_stmt.executeQuery();
            while(rs.next())
            {
                result.getCategory().add(ReadCategoryFromDS(rs));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public Category Create(Category value) {
        try {
            proc_stmt = DAOConnection.prepareCall("{ call spcatogeryInsert(?,?) }");
            proc_stmt.registerOutParameter("CatogeryID", java.sql.Types.INTEGER);
            proc_stmt.setString("CatogeryName", value.getCatogeryName());
            proc_stmt.execute();
            value.setCatogeryID(proc_stmt.getLong("CatogeryID"));
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return value;
    }

    public Category Edit(Category value) {
        try {
            proc_stmt = DAOConnection.prepareCall("{ call spcatogeryUpdate(?,?) }");
            proc_stmt.setLong("CatogeryID", value.getCatogeryID());
            proc_stmt.setString("CatogeryName", value.getCatogeryName());
            proc_stmt.execute();
            return value;
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public boolean Delete(int value) {
        try {
            proc_stmt = DAOConnection.prepareCall("{ call spcatogeryDelete(?) }");
            proc_stmt.setLong("CatogeryID", value);
            return  proc_stmt.execute();
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public Categories Search(String name) {
        Categories result = new Categories();
        try {
            proc_stmt = DAOConnection.prepareCall("{ call spcatogerySearch(?) }");
            proc_stmt.setString("CatogeryName", name);
            rs = proc_stmt.executeQuery();
            while(rs.next())
            {
                result.getCategory().add(ReadCategoryFromDS(rs));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }
}
